﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhongMachTu.OModel;

namespace PhongMachTu.OConnection
{
    static class DataManipulate
    {
        private static String connectionStr = "Server=localhost;Database=phongmachtu;User ID=phongmachtu;Password=phongmachtu;Trusted_Connection=False;";
        //private static String connectionStr = "Server=localhost;Database=phongmachtu;User ID=sa;Password=1234567;Trusted_Connection=False;";
        private static SqlConnection con = new SqlConnection(connectionStr);

        //For Login Form, get User
        public static User GetUserFromCredential(string uname, string pwd)
        {
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            string sql = "select * from [user] where user_email = '" + uname + "' and user_password = '" + pwd + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable user = new DataTable();
            da.Fill(user);

            if (user.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow userinfo = user.Rows[0];
                User loggedUser = new User(Convert.ToInt32(userinfo["user_id"]), userinfo["user_email"].ToString(), userinfo["user_password"].ToString(), Convert.ToInt32(userinfo["user_type"]));
                con.Close();
                return loggedUser;
            }
        }

        //For Setting Form, get Fee
        public static SettingFee getSettingFee()
        {
            string sql = "select va_id, va_value from variable where va_id = 2";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable curFee = new DataTable();
            da.Fill(curFee);

            if (curFee.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = curFee.Rows[0];
                SettingFee fee = new SettingFee(Convert.ToInt32(row["va_id"]), Convert.ToInt32(row["va_value"]));
                con.Close();
                return fee;
            }
        }

        //For Setting Form, get MaxPatient
        public static SettingMaxPatientPerDay getSettingMaxPatient()
        {
            string sql = "select va_id, va_value from variable where va_id = 1";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable max = new DataTable();
            da.Fill(max);

            if (max.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = max.Rows[0];
                SettingMaxPatientPerDay maxVal = new SettingMaxPatientPerDay(Convert.ToInt32(row["va_id"]), Convert.ToInt32(row["va_value"]));
                con.Close();
                return maxVal;
            }
        }

        //For Setting Form, update Fee
        public static bool updateFee(SettingFee fee)
        {
            string sql = "update variable set va_value = " + fee._value + " where va_id = 2";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }

            SqlCommand command = new SqlCommand(sql, con);
            int result = command.ExecuteNonQuery();

            command = null;
            con.Close();

            return result > 0;
        }

        //For Setting Form, update MaxPatient
        public static bool updateMaxPatient(SettingMaxPatientPerDay maxPatient)
        {
            string sql = "update variable set va_value = " + maxPatient._value + " where va_id = 1";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }

            SqlCommand command = new SqlCommand(sql, con);
            int result = command.ExecuteNonQuery();

            command = null;
            con.Close();

            return result > 0;
        }

        //For Disease Form, get List of Diseases
        public static ArrayList getDiseaseList()
        {
            string sql = "select di_id, di_name from disease order by di_name ASC";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable diseases = new DataTable();
            da.Fill(diseases);

            if (diseases.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList dsDiseases = new ArrayList();
                for (int i = 0; i < diseases.Rows.Count; i++)
                {
                    DataRow row = diseases.Rows[i];
                    Disease disease = new Disease(Convert.ToInt32(row["di_id"]), row["di_name"].ToString());
                    dsDiseases.Add(disease);
                }
                con.Close();
                return dsDiseases;
            }
        }

        //For Disease Form, check exist disease
        public static bool isDiseaseExist(string title)
        {
            string sql = "select count(di_id) from disease where di_name = N'" + title + "'";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return true;
            }

            SqlCommand cmd = new SqlCommand(sql, con);
            if (Int32.Parse(cmd.ExecuteScalar().ToString()) == 0)
            {
                //exist
                con.Close();
                return false;
            }
            else
            {
                //not exist
                con.Close();
                return true;
            }
        }

        //For Disease Form, add new disease
        public static bool addDisease(string title)
        {
            string sql = "insert into disease values(N'" + title + "')";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Disease Form, update disease
        public static bool updateDisease(Disease disease)
        {
            string sql = "update disease set di_name = N'" + disease.NameDisease + "' where di_id = " + disease.IDDisease;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Disease Form, check availability to delete disease
        public static bool isDiseaseDeletable(Disease disease)
        {
            string sql = "select count(vi_disease) from visit where vi_disease = " + disease.IDDisease;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return result == 0;
        }

        //For Disease Form, delete disease
        public static bool deleteDisease(Disease disease)
        {
            string sql = "delete from disease where di_id = " + disease.IDDisease;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, display all Medicine
        public static ArrayList getAllMedicine()
        {
            string sql = "select m.me_id, m.me_name, m.me_price, mu.muse_id, mu.muse_text, mun.unit_id, mun.unit_name ";
            sql += "from medicine m ";
            sql += "inner join medicine_using mu on m.me_using = mu.muse_id ";
            sql += "inner join medicine_unit mun on m.me_unit = mun.unit_id ";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable medicines = new DataTable();
            da.Fill(medicines);

            if (medicines.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList dsMedicines = new ArrayList();
                for (int i = 0; i < medicines.Rows.Count; i++)
                {
                    DataRow row = medicines.Rows[i];
                    Medicine medicine = new Medicine(Convert.ToInt32(row["me_id"]), row["me_name"].ToString(), Convert.ToDouble(row["me_price"]), new MedicineUnit(Convert.ToInt32(row["unit_id"]), row["unit_name"].ToString()), new MedicineUsing(Convert.ToInt32(row["muse_id"]), row["muse_text"].ToString()));

                    dsMedicines.Add(medicine);
                }
                con.Close();
                return dsMedicines;
            }
        }

        //For Medicine Form, display all Medicine Unit
        public static ArrayList getAllMedicineUnit()
        {
            string sql = "select unit_id, unit_name from medicine_unit";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable units = new DataTable();
            da.Fill(units);

            if (units.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList dsUnits = new ArrayList();
                for (int i = 0; i < units.Rows.Count; i++)
                {
                    DataRow row = units.Rows[i];
                    MedicineUnit unit = new MedicineUnit(Convert.ToInt32(row["unit_id"]), row["unit_name"].ToString());

                    dsUnits.Add(unit);
                }
                con.Close();
                return dsUnits;
            }
        }

        //For Medicine Form, display all Medicine Using
        public static ArrayList getAllMedicineUsing()
        {
            string sql = "select muse_id, muse_text from medicine_using";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable usings = new DataTable();
            da.Fill(usings);

            if (usings.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList dsUsings = new ArrayList();
                for (int i = 0; i < usings.Rows.Count; i++)
                {
                    DataRow row = usings.Rows[i];
                    MedicineUsing ausing = new MedicineUsing(Convert.ToInt32(row["muse_id"]), row["muse_text"].ToString());

                    dsUsings.Add(ausing);
                }
                con.Close();
                return dsUsings;
            }
        }

        //For Medicine Form, check Medicine Unit Exist
        public static bool isUnitExist(string input)
        {
            string sql = "select count(unit_id) from medicine_unit where unit_name = N'" + input + "'";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return true;
            }

            SqlCommand cmd = new SqlCommand(sql, con);
            if (Int32.Parse(cmd.ExecuteScalar().ToString()) == 0)
            {
                //exist
                con.Close();
                return false;
            }
            else
            {
                //not exist
                con.Close();
                return true;
            }
        }

        //For Medicine Form, add Medicine Unit
        public static bool addUnit(string input)
        {
            string sql = "insert into medicine_unit values(N'" + input + "')";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, check Unit Deletable
        public static bool isUnitDeletable(MedicineUnit unit)
        {
            string sql = "select count(me_unit) from medicine where me_unit = " + unit.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return result == 0;
        }

        //For Medicine Form, delete Unit
        public static bool deleteUnit(MedicineUnit unit)
        {
            string sql = "delete from medicine_unit where unit_id = " + unit.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, update Unit
        public static bool updateUnit(MedicineUnit unit)
        {
            string sql = "update medicine_unit set unit_name = N'" + unit.Name + "' where unit_id = " + unit.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, check Using Exist
        public static bool isUsingExist(string input)
        {
            string sql = "select count(muse_id) from medicine_using where muse_text = N'" + input + "'";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return true;
            }

            SqlCommand cmd = new SqlCommand(sql, con);
            if (Int32.Parse(cmd.ExecuteScalar().ToString()) == 0)
            {
                //exist
                con.Close();
                return false;
            }
            else
            {
                //not exist
                con.Close();
                return true;
            }
        }

        //For Medicine Form, add Using
        public static bool addUsing(string input)
        {
            string sql = "insert into medicine_using values(N'" + input + "')";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, update Using
        public static bool updateUsing(MedicineUsing ausing)
        {
            string sql = "update medicine_using set muse_text = N'" + ausing.Name + "' where muse_id = " + ausing.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        //For Medicine Form, check Using deletable
        public static bool isUsingDeletable(MedicineUsing ausing)
        {
            string sql = "select count(me_using) from medicine where me_using = " + ausing.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return result == 0;
        }

        //For Medicine Form, delete Using
        public static bool deleteUsing(MedicineUsing ausing)
        {
            string sql = "delete from medicine_using where muse_id = " + ausing.Id;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static MedicineUnit getUnitByName(string unitName)
        {
            string sql = "select * from medicine_unit where unit_name = N'" + unitName + "'";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable units = new DataTable();
            da.Fill(units);

            if (units.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = units.Rows[0];
                MedicineUnit aUnit = new MedicineUnit(Convert.ToInt32(row["unit_id"]), row["unit_name"].ToString());
                con.Close();
                return aUnit;
            }

        }

        public static MedicineUsing getUsingByName(string usingName)
        {
            string sql = "select * from medicine_using where muse_text = N'" + usingName + "'";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable usings = new DataTable();
            da.Fill(usings);

            if (usings.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = usings.Rows[0];
                MedicineUsing aUsing = new MedicineUsing(Convert.ToInt32(row["muse_id"]), row["muse_text"].ToString());
                con.Close();
                return aUsing;
            }
        }

        public static bool isMedicineExist(string name)
        {
            string sql = "select count(me_id) from medicine where me_name = N'" + name + "'";

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return true;
            }

            SqlCommand cmd = new SqlCommand(sql, con);
            if (Int32.Parse(cmd.ExecuteScalar().ToString()) == 0)
            {
                //exist
                con.Close();
                return false;
            }
            else
            {
                //not exist
                con.Close();
                return true;
            }
        }

        public static bool addMedicine(Medicine medicine)
        {
            string sql = "insert into medicine( me_name, me_price, me_using, me_unit) values(N'" + medicine.Name + "'," + medicine.Price + "," + medicine.Using.Id + "," + medicine.Unit.Id + ")";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static bool updateMedicine(Medicine medicine)
        {
            string sql = "update medicine set me_name = N'" + medicine.Name + "', me_price = " + medicine.Price + ", me_using = " + medicine.Using.Id + ", me_unit=" + medicine.Unit.Id + " where me_id = " + medicine.IdMedicine;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static bool isMedicineDeletable(Medicine medicine)
        {
            string sql = "select count(vme_id) from visit_medicine where vme_medicine = " + medicine.IdMedicine;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return result == 0;
        }

        public static bool deleteMedicine(Medicine medicine)
        {
            string sql = "delete from medicine where me_id = " + medicine.IdMedicine;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static ArrayList getPatientInDay()
        {
            string sql = "SELECT * FROM PATIENT PA, visit VI WHERE PA.pa_id=VI.vi_patient AND VI.vi_date='" + DateTime.Now.ToString("MM/dd/yyyy") + "'";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList patientList = new ArrayList();
                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];
                    Patient patient = new Patient(int.Parse(row["pa_id"].ToString()), row["pa_fullname"].ToString()
                        , DateTime.Parse(row["pa_birthyear"].ToString()), bool.Parse(row["pa_gender"].ToString())
                        , row["pa_address"].ToString(), int.Parse(row["vi_id"].ToString()));

                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static ArrayList getPatientList()
        {
            string sql = "SELECT * FROM PATIENT PA, visit VI WHERE PA.pa_id=VI.vi_patient AND VI.vi_date='" + DateTime.Now.ToString("MM/dd/yyyy") + "' AND VI.vi_paid=0 AND VI.vi_symptom IS NULL AND VI.vi_disease IS NULL";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList patientList = new ArrayList();
                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];
                    Patient patient = new Patient(int.Parse(row["pa_id"].ToString()), row["pa_fullname"].ToString(), DateTime.Parse(row["pa_birthyear"].ToString()), bool.Parse(row["pa_gender"].ToString()), row["pa_address"].ToString());

                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static ArrayList getAllPatient()
        {
            string sql = "SELECT * FROM PATIENT";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList patientList = new ArrayList();
                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];
                    Patient patient = new Patient(int.Parse(row["pa_id"].ToString()), row["pa_fullname"].ToString(), DateTime.Parse(row["pa_birthyear"].ToString()), bool.Parse(row["pa_gender"].ToString()), row["pa_address"].ToString());

                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static ArrayList searchPatient(string p)
        {
            string sql = "SELECT * FROM PATIENT WHERE PA_FULLNAME LIKE N'%" + p + "%'";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList patientList = new ArrayList();
                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];

                    Patient patient = new Patient();
                    patient.PatienId = int.Parse(row["pa_id"].ToString());
                    patient.Fullname = row["pa_fullname"].ToString();
                    patient.Dob = DateTime.Parse(row["pa_birthyear"].ToString());
                    patient.Sex = bool.Parse(row["pa_gender"].ToString());
                    patient.Address = row["pa_address"].ToString();

                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static Patient getPatientByID(int ID)
        {
            string sql = "SELECT * FROM PATIENT WHERE PA_ID =" + ID;
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtPa = new DataTable();
            da.Fill(dtPa);
            Patient patient;
            if (dtPa.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = dtPa.Rows[0];
                patient = new Patient(int.Parse(row["pa_id"].ToString()), row["pa_fullname"].ToString(), DateTime.Parse(row["pa_birthyear"].ToString()), bool.Parse(row["pa_gender"].ToString()), row["pa_address"].ToString());
            }
            con.Close();
            return patient;
        }

        public static int saveVisit(int id, int diseaseID, String sympton)
        {
            int viID = getViID(id);
            int result = 0;
            if (viID > 0)
            {
                string sql = "update visit set vi_disease = " + diseaseID + ", vi_symptom = N'" + sympton + "' where vi_id = " + viID;
                try
                {
                    con.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine("Cannot connect DB");
                    Console.WriteLine(e.Message);
                    con.Close();
                    return 0;
                }
                SqlCommand command = new SqlCommand(sql, con);
                result = command.ExecuteNonQuery();

                command = null;
                con.Close();

                return viID;
            }
            else
            {
                string sql = "insert into visit values('" + DateTime.Now.ToString("MM/dd/yyyy") + "',N'" + sympton + "',0,'false'," + id + "," + diseaseID + ")";
                SqlCommand cmd = new SqlCommand(sql, con);

                try
                {
                    con.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine("Cannot connect DB");
                    Console.WriteLine(e.Message);
                    con.Close();
                    return 0;
                }
                result = cmd.ExecuteNonQuery();
                con.Close();
                viID = getViID(id);
                return viID;
            }
        }

        public static int saveVisit(int id)
        {
            int viID = 0;
            int result = 0;
            string sql = "insert into visit values('" + DateTime.Now.ToString("MM/dd/yyyy") + "',NULL,0,'false'," + id + ",NULL)";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return 0;
            }
            result = cmd.ExecuteNonQuery();
            con.Close();
            viID = getViID(id);
            return viID;

        }

        public static int getViID(int id)
        {
            string checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_PATIENT = " + id
                            + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy") + "' AND VI_PAID = 0";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return -1;
            }
            int viID = 0;
            SqlCommand checkCmd = new SqlCommand(checkQuery, con);
            int soluong = (int)checkCmd.ExecuteScalar();
            con.Close();
            if (soluong > 0)
            {
                checkQuery = "SELECT VI_ID FROM VISIT WHERE VI_PATIENT = " + id
                            + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy") + "' AND VI_PAID = 0";

                checkCmd = new SqlCommand(checkQuery, con);
                con.Open();
                viID = (int)checkCmd.ExecuteScalar();
                con.Close();

            }
            return viID;
        }

        public static int getPatientStat(int id, int viID)
        {
            int result = 0;
            string checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_ID = " + viID + " AND VI_PATIENT = " + id
                            + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                            + "' AND VI_DISEASE IS NULL AND VI_SYMPTOM IS NULL";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return 6;
            }

            SqlCommand checkCmd = new SqlCommand(checkQuery, con);
            int soLuong = (int)checkCmd.ExecuteScalar();
            con.Close();

            if (soLuong > 0)
            {
                result = 1;
            }
            else
            {
                checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_ID = " + viID + " AND VI_PATIENT = " + id
                           + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                           + "' AND VI_DISEASE IS NULL AND VI_SYMPTOM IS NOT NULL";
                checkCmd = new SqlCommand(checkQuery, con);
                con.Open();
                soLuong = (int)checkCmd.ExecuteScalar();
                con.Close();
                if (soLuong > 0)
                {
                    result = 2;
                }
                else
                {
                    checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_ID = " + viID + " AND VI_PATIENT = " + id
                               + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                               + "' AND VI_DISEASE IS NOT NULL AND VI_SYMPTOM IS NULL";
                    checkCmd = new SqlCommand(checkQuery, con);
                    con.Open();
                    soLuong = (int)checkCmd.ExecuteScalar();
                    con.Close();
                    if (soLuong > 0)
                    {
                        result = 3;
                    }
                    else
                    {
                        checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_ID = " + viID + " AND VI_PATIENT = " + id
                                   + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                                   + "' AND VI_DISEASE IS NOT NULL AND VI_SYMPTOM IS NOT NULL";
                        checkCmd = new SqlCommand(checkQuery, con);
                        con.Open();
                        soLuong = (int)checkCmd.ExecuteScalar();
                        con.Close();
                        if (soLuong > 0)
                        {
                            checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_ID = " + viID + " AND VI_PATIENT = " + id
                                      + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                                      + "' AND VI_DISEASE IS NOT NULL AND VI_SYMPTOM IS NOT NULL AND VI_PAID = 1";
                            checkCmd = new SqlCommand(checkQuery, con);
                            con.Open();
                            soLuong = (int)checkCmd.ExecuteScalar();
                            con.Close();
                            if (soLuong > 0)
                            {
                                result = 5;
                            }
                            else
                            {
                                result = 4;
                            }
                        }
                        else
                        {
                            result = 0;
                        }
                    }
                }
            }
            return result;
        }

        public static bool checkPatientInDay(int id)
        {
            bool result = false;
            string checkQuery = "SELECT COUNT(*) FROM VISIT WHERE VI_PATIENT = " + id
                            + " AND VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy")
                            + "' AND VI_PAID = 0";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return result;
            }

            SqlCommand checkCmd = new SqlCommand(checkQuery, con);
            int soLuong = (int)checkCmd.ExecuteScalar();
            con.Close();

            if (soLuong > 0)
            {
                result = true;
            }
            return result;
        }

        public static bool saveViMed(int viID, int quantity, int medID, double price)
        {
            int result = 0;
            string sql = "insert into visit_medicine values(" + viID + "," + quantity + "," + medID + "," + price + ")";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            result = cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static bool updateViMed(int vmeID, int quantity, int medID, double price)
        {
            string sql = "update visit_medicine set vme_quantity = " + quantity
                       + ", vme_medicine = " + medID + ", vme_price = " + price
                       + " where vme_id = " + vmeID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static bool checkViMed(int viID, int medID)
        {
            string sql = "SELECT COUNT(*) FROM VISIT_MEDICINE WHERE VME_VISIT = " + viID + " AND VME_MEDICINE = " + medID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result > 0;
        }

        public static bool deleteViMed(int vmeID)
        {
            string sql = "delete visit_medicine where vme_id = " + vmeID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result == 1;
        }

        public static Medicine getMedByID(int id)
        {
            string sql = "select * from medicine m, medicine_unit mu, medicine_using mus "
                        + "where m.me_unit=mu.unit_id and m.me_using = mus.muse_id and m.me_id = " + id;
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtMed = new DataTable();
            da.Fill(dtMed);
            Medicine med;
            if (dtMed.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = dtMed.Rows[0];
                med = new Medicine(int.Parse(row["me_id"].ToString()), row["me_name"].ToString(), double.Parse(row["me_price"].ToString()),
                    new MedicineUnit(int.Parse(row["me_unit"].ToString()), row["unit_name"].ToString()),
                    new MedicineUsing(int.Parse(row["me_using"].ToString()), row["muse_text"].ToString()));
            }
            con.Close();
            return med;
        }

        public static VisitMedicine getViMedByID(int vmeID, int viID)
        {
            VisitMedicine vme;
            string sql = "SELECT * FROM visit_medicine VME, visit VI WHERE VI.vi_id = VME.vme_visit "
                       + "AND VI.vi_paid = 0 AND VME.vme_id = " + vmeID + " AND VME.vme_visit = " + viID;
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtMed = new DataTable();
            da.Fill(dtMed);
            if (dtMed.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = dtMed.Rows[0];
                con.Close();
                vme = new VisitMedicine(int.Parse(row["vme_id"].ToString()), viID, int.Parse(row["vme_quantity"].ToString()), getMedByID(int.Parse(row["vme_medicine"].ToString())));
            }

            return vme;
        }

        public static ArrayList getViMed(int viID)
        {
            string sql = "SELECT * FROM VISIT_MEDICINE WHERE vme_visit = " + viID;

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable visitMeds = new DataTable();
            da.Fill(visitMeds);

            if (visitMeds.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList viMeds = new ArrayList();
                for (int i = 0; i < visitMeds.Rows.Count; i++)
                {
                    DataRow row = visitMeds.Rows[i];
                    con.Close();
                    int medID = int.Parse(row["vme_medicine"].ToString());
                    VisitMedicine viMed = new VisitMedicine(int.Parse(row["vme_id"].ToString()), int.Parse(row["vme_visit"].ToString()), int.Parse(row["vme_quantity"].ToString()), getMedByID(medID));
                    viMeds.Add(viMed);
                    con.Open();
                }
                con.Close();
                return viMeds;
            }
        }

        public static ArrayList searchPaInfo(string p)
        {
            string sql = "SELECT * FROM patient PA, visit VI, disease DI " +
                    "WHERE PA.pa_id=VI.vi_patient AND VI.vi_disease = DI.di_id" +
                         " AND PA.pa_fullname LIKE N'%" + p + "%'" +
                    "ORDER BY PA.pa_id ASC, VI.vi_date ASC";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                ArrayList patientList = new ArrayList();
                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];
                    int diseaseID = 0;
                    if (row["vi_disease"].ToString() != null)
                    {
                        diseaseID = int.Parse(row["vi_disease"].ToString());
                    }
                    Patient patient = new Patient();
                    patient.PatienId = int.Parse(row["pa_id"].ToString());
                    patient.Fullname = row["pa_fullname"].ToString();
                    patient.Dob = DateTime.Parse(row["pa_birthyear"].ToString());
                    patient.Sex = bool.Parse(row["pa_gender"].ToString());
                    patient.Address = row["pa_address"].ToString();
                    patient.VisitDate = DateTime.Parse(row["vi_date"].ToString()).ToString("dd/MM/yyyy");
                    patient.Symptom = row["vi_symptom"].ToString();
                    patient.Disease = row["di_name"].ToString();
                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static bool checkPaInfo(string fullname, string address, bool gender, int birthyear)
        {
            string sql = "SELECT COUNT(*) FROM PATIENT WHERE PA_FULLNAME = N'" + fullname + "' AND PA_GENDER = '" + gender + "'  AND YEAR(PA_BIRTHYEAR) ='" + birthyear + "' AND PA_ADDRESS = N'" + address + "'";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result > 0;
        }

        public static bool checkPaInfo(int paID, string fullname, string address, bool gender, int birthyear)
        {
            string sql = "SELECT COUNT(*) FROM PATIENT WHERE PA_FULLNAME = N'" + fullname + "' AND PA_GENDER = '" + gender
                      + "' AND YEAR(PA_BIRTHYEAR) ='" + birthyear + "' AND PA_ADDRESS = N'" + address
                      + "' AND PA_ID <> " + paID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result > 0;
        }

        public static bool updatePatient(int paID, string fullname, string address, bool gender, int birthyear)
        {
            string sql = "UPDATE PATIENT SET PA_FULLNAME = N'" + fullname + "', PA_GENDER = '" + gender
                        + "', PA_BIRTHYEAR = '01/01/" + birthyear + "', PA_ADDRESS = N'" + address
                        + "' WHERE PA_ID = " + paID;
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static bool addPatient(string fullname, string address, bool gender, int birthyear)
        {
            string sql = "INSERT INTO PATIENT VALUES (N'" + fullname + "','" + gender + "','01/01/" + birthyear + "',N'" + address + "')";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static int getPaID(string fullname, string address, bool gender, int birthyear)
        {
            string sql = "SELECT PA_ID FROM PATIENT WHERE PA_FULLNAME = N'" + fullname + "' AND PA_GENDER = '" + gender + "'  AND YEAR(PA_BIRTHYEAR) ='" + birthyear + "' AND PA_ADDRESS = N'" + address + "'";
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return 0;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result;
        }

        public static bool checkDelVisit(int viID)
        {
            string sql = "SELECT COUNT(*) FROM VISIT WHERE VI_DISEASE IS NOT NULL AND VI_ID = " + viID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result > 0;
        }

        public static bool delVisit(int viID)
        {
            string sql = "delete visit where vi_id = " + viID;
            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static Visit getVisitInfo(int viID, int caseID)
        {
            Visit visit = new Visit();
            string sql = "SELECT * FROM VISIT WHERE VI_ID = " + viID;
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            } SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtMed = new DataTable();
            da.Fill(dtMed);
            if (dtMed.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                DataRow row = dtMed.Rows[0];
                con.Close();
                if (caseID == 2)
                {
                    visit.IdVisit = int.Parse(row["vi_id"].ToString());
                    visit.VisitDate = DateTime.Parse(row["vi_date"].ToString());
                    visit.Symtom = row["vi_symptom"].ToString();
                    visit.Price = double.Parse(row["vi_price"].ToString());
                    visit.Paid = bool.Parse(row["vi_paid"].ToString());
                    visit.DiseaseID = 0;
                }
                else if (caseID == 3)
                {
                    visit.IdVisit = int.Parse(row["vi_id"].ToString());
                    visit.VisitDate = DateTime.Parse(row["vi_date"].ToString());
                    visit.Symtom = "";
                    visit.Price = double.Parse(row["vi_price"].ToString());
                    visit.Paid = bool.Parse(row["vi_paid"].ToString());
                    visit.DiseaseID = int.Parse(row["vi_disease"].ToString());
                }
                else
                {
                    visit.IdVisit = int.Parse(row["vi_id"].ToString());
                    visit.VisitDate = DateTime.Parse(row["vi_date"].ToString());
                    visit.Symtom = row["vi_symptom"].ToString();
                    visit.Price = double.Parse(row["vi_price"].ToString());
                    visit.Paid = bool.Parse(row["vi_paid"].ToString());
                    visit.DiseaseID = int.Parse(row["vi_disease"].ToString());
                }
            }
            return visit;
        }

        public static ArrayList getPaForPay()
        {
            ArrayList patientList = new ArrayList();
            string sql = "SELECT * FROM PATIENT PA, visit VI WHERE PA.pa_id=VI.vi_patient AND VI.vi_disease is not null AND VI.vi_paid = 0";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable patients = new DataTable();
            da.Fill(patients);

            if (patients.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {

                for (int i = 0; i < patients.Rows.Count; i++)
                {
                    DataRow row = patients.Rows[i];

                    Patient patient = new Patient();
                    patient.PatienId = int.Parse(row["pa_id"].ToString());
                    patient.Fullname = row["pa_fullname"].ToString();
                    patient.VisitDate = DateTime.Parse(row["vi_date"].ToString()).ToString("dd/MM/yyyy");
                    patient.ViID = int.Parse(row["vi_id"].ToString());
                    patientList.Add(patient);
                }
                con.Close();
                return patientList;
            }
        }

        public static bool pay(int viID, double price)
        {
            string sql = "UPDATE visit SET vi_price = " + price + " , vi_paid = 1 WHERE vi_id = " + viID;
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static int getMinViYear()
        {
            string sql = "select YEAR(vi_date) from visit where YEAR(vi_date) <= all(select YEAR(vi_date)from visit)";
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return int.Parse(DateTime.Now.ToString("yyyy"));
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result;
        }

        public static ArrayList getMedReport(int month, int year)
        {
            ArrayList report = new ArrayList(); ;
            string sql = "SELECT M.me_id,SUM(VME.vme_quantity) as quantity,COUNT(vme_medicine) as useTime FROM medicine M, visit_medicine VME, visit VI "
                    + "WHERE M.me_id=VME.vme_medicine AND VI.vi_id = VME.vme_visit AND MONTH(VI.vi_date) =" + month + " AND YEAR(VI.vi_date)= " + year
                    + " Group by M.me_id";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtReport = new DataTable();
            da.Fill(dtReport);

            if (dtReport.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                for (int i = 0; i < dtReport.Rows.Count; i++)
                {
                    DataRow row = dtReport.Rows[i];
                    Medicine m = new Medicine();
                    m.IdMedicine = int.Parse(row["me_id"].ToString());
                    m.QuantityUse = int.Parse(row["quantity"].ToString());
                    m.UseTime = int.Parse(row["useTime"].ToString());
                    report.Add(m);
                }
                con.Close();
                return report;
            }

        }

        public static double getTotalRevenue(int month, int year)
        {
            string sql = "SELECT SUM(vi_price) FROM visit WHERE vi_paid =1 AND MONTH(vi_date) = " + month + " AND YEAR(vi_date) = " + year;

            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return 0.0;
            }
            double result = (double)cmd.ExecuteScalar();
            con.Close();
            return result;
        }

        public static ArrayList getMonthlyReport(int month, int year)
        {
            ArrayList report = new ArrayList(); ;
            string sql = "SELECT vi_date, COUNT(vi_patient) AS numOfPa,SUM(vi_price) AS revenue FROM visit WHERE vi_paid = 1 AND MONTH(vi_date) = " + month + " AND YEAR(vi_date) = " + year + " GROUP BY vi_date";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable dtReport = new DataTable();
            da.Fill(dtReport);

            if (dtReport.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                for (int i = 0; i < dtReport.Rows.Count; i++)
                {
                    DataRow row = dtReport.Rows[i];
                    double revenue = double.Parse(row["revenue"].ToString());
                    con.Close();
                    double totalRev = getTotalRevenue(month, year);
                    con.Open();
                    Visit v = new Visit(DateTime.Parse(row["vi_date"].ToString()), int.Parse(row["numOfPa"].ToString())
                                       , revenue, (revenue / totalRev));
                    report.Add(v);
                }
                con.Close();
                return report;
            }

        }

        public static int getNumOfPa()
        {
            string sql = "SELECT COUNT(*) FROM VISIT WHERE VI_DATE = '" + DateTime.Now.ToString("MM/dd/yyyy") + "'";

            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return 0;
            }
            int result = (int)cmd.ExecuteScalar();
            con.Close();
            return result;
        }

        public static bool addNewUser(string username, string password, int type)
        {
            string sql = "insert into [user] values('" + username + "','" + GetMD5Hash(password) + "'," + type + ")";

            SqlCommand cmd = new SqlCommand(sql, con);

            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static ArrayList getAllUsers()
        {
            string sql = "select * from [user]";
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return null;
            }
            ArrayList users = new ArrayList();

            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            DataTable accs = new DataTable();
            da.Fill(accs);

            if (accs.Rows.Count < 1)
            {
                con.Close();
                return null;
            }
            else
            {
                for (int i = 0; i < accs.Rows.Count; i++)
                {
                    DataRow row = accs.Rows[i];
                    User user = new User(Convert.ToInt32(row["user_id"]), row["user_email"].ToString(), row["user_password"].ToString(), Convert.ToInt32(row["user_type"]));
                    users.Add(user);
                }
                con.Close();
                return users;
            }
        }

        //Produce MD5 from String
        public static string GetMD5Hash(string input)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
            byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);
            bs = x.ComputeHash(bs);
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            foreach (byte b in bs)
            {
                s.Append(b.ToString("x2").ToLower());
            }
            string password = s.ToString();
            return password;
        }

        public static bool deleteUser(int userID)
        {
            string sql = "delete from [user] where user_id = " + userID;

            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }

        public static bool updatePassword(int id, string pwd)
        {
            string sql = "update [user] set user_password = '" + GetMD5Hash(pwd) + "' where user_id = " + id;
            SqlCommand cmd = new SqlCommand(sql, con);
            try
            {
                con.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine("Cannot connect DB");
                Console.WriteLine(e.Message);
                con.Close();
                return false;
            }
            int result = (int)cmd.ExecuteNonQuery();
            con.Close();
            return result > 0;
        }
    }
}
